{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Populating the interactive namespace from numpy and matplotlib\n"
     ]
    }
   ],
   "source": [
    "%pylab inline\n",
    "plt.style.use(\"bmh\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pathlib\n",
    "import pandas as pd\n",
    "from catboost import CatBoostRegressor\n",
    "from sklearn.metrics import mean_squared_log_error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "# импортируем классы KFold, TimeSeriesSplit и GroupKFold,\n",
    "# реализующие стратегии перекрестной проверки, и\n",
    "# класс GridSearchCV для поиска гиперпараметров\n",
    "from sklearn.model_selection import (\n",
    "    KFold,\n",
    "    TimeSeriesSplit, \n",
    "    GroupKFold,\n",
    "    GridSearchCV\n",
    ")\n",
    "\n",
    "from category_encoders import TargetEncoder, LeaveOneOutEncoder , OrdinalEncoder\n",
    "import warnings\n",
    "import xgboost as xgb\n",
    "import lightgbm as lgb\n",
    "import joblib\n",
    "\n",
    "from sklearn.ensemble import RandomForestRegressor, StackingRegressor, GradientBoostingRegressor\n",
    "# увеличиваем количество отображаемых столбцов\n",
    "pd.set_option('display.max_columns', 50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.0.1\n"
     ]
    }
   ],
   "source": [
    "import sklearn\n",
    "print (sklearn.__version__)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "DATA_DIR = pathlib.Path(\".\")\n",
    "DATA_FILE = \"sc2021_train_deals.csv\"\n",
    "AGG_COLS = [\"material_code\", \"company_code\", \"country\", \"region\", \"manager_code\"]\n",
    "RS = 82736"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Загрузка данных"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(DATA_DIR.joinpath(DATA_FILE), parse_dates=[\"month\", \"date\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>material_code</th>\n",
       "      <th>company_code</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>manager_code</th>\n",
       "      <th>month</th>\n",
       "      <th>material_lvl1_name</th>\n",
       "      <th>material_lvl2_name</th>\n",
       "      <th>material_lvl3_name</th>\n",
       "      <th>contract_type</th>\n",
       "      <th>date</th>\n",
       "      <th>volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>134</td>\n",
       "      <td>0</td>\n",
       "      <td>Литва</td>\n",
       "      <td>Литва</td>\n",
       "      <td>12261</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>Базовые полимеры</td>\n",
       "      <td>ПЭ</td>\n",
       "      <td>ПЭНП</td>\n",
       "      <td>Спот</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>197</td>\n",
       "      <td>0</td>\n",
       "      <td>Китай</td>\n",
       "      <td>Китай</td>\n",
       "      <td>16350</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>Базовые полимеры</td>\n",
       "      <td>ПЭ</td>\n",
       "      <td>ПЭНП</td>\n",
       "      <td>Спот</td>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>794</td>\n",
       "      <td>2162</td>\n",
       "      <td>Казахстан</td>\n",
       "      <td>Атырауская обл.</td>\n",
       "      <td>10942</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>Базовые полимеры</td>\n",
       "      <td>ПП</td>\n",
       "      <td>ПП</td>\n",
       "      <td>Контракт</td>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>134</td>\n",
       "      <td>0</td>\n",
       "      <td>Литва</td>\n",
       "      <td>Литва</td>\n",
       "      <td>12261</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>Базовые полимеры</td>\n",
       "      <td>ПЭ</td>\n",
       "      <td>ПЭНП</td>\n",
       "      <td>Спот</td>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Китай</td>\n",
       "      <td>Китай</td>\n",
       "      <td>17745</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>Базовые полимеры</td>\n",
       "      <td>ПЭ</td>\n",
       "      <td>ПЭНП</td>\n",
       "      <td>Спот</td>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   material_code  company_code    country           region  manager_code  \\\n",
       "0            134             0      Литва            Литва         12261   \n",
       "1            197             0      Китай            Китай         16350   \n",
       "2            794          2162  Казахстан  Атырауская обл.         10942   \n",
       "3            134             0      Литва            Литва         12261   \n",
       "4            133             0      Китай            Китай         17745   \n",
       "\n",
       "       month material_lvl1_name material_lvl2_name material_lvl3_name  \\\n",
       "0 2018-01-01   Базовые полимеры                 ПЭ               ПЭНП   \n",
       "1 2018-01-01   Базовые полимеры                 ПЭ               ПЭНП   \n",
       "2 2018-01-01   Базовые полимеры                 ПП                 ПП   \n",
       "3 2018-01-01   Базовые полимеры                 ПЭ               ПЭНП   \n",
       "4 2018-01-01   Базовые полимеры                 ПЭ               ПЭНП   \n",
       "\n",
       "  contract_type       date  volume  \n",
       "0          Спот 2018-01-01    43.0  \n",
       "1          Спот 2018-01-02    95.0  \n",
       "2      Контракт 2018-01-02    57.0  \n",
       "3          Спот 2018-01-02    21.0  \n",
       "4          Спот 2018-01-02   150.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 92306 entries, 0 to 92305\n",
      "Data columns (total 12 columns):\n",
      " #   Column              Non-Null Count  Dtype         \n",
      "---  ------              --------------  -----         \n",
      " 0   material_code       92306 non-null  int64         \n",
      " 1   company_code        92306 non-null  int64         \n",
      " 2   country             92306 non-null  object        \n",
      " 3   region              92306 non-null  object        \n",
      " 4   manager_code        92306 non-null  int64         \n",
      " 5   month               92306 non-null  datetime64[ns]\n",
      " 6   material_lvl1_name  92306 non-null  object        \n",
      " 7   material_lvl2_name  92306 non-null  object        \n",
      " 8   material_lvl3_name  92306 non-null  object        \n",
      " 9   contract_type       92306 non-null  object        \n",
      " 10  date                92306 non-null  datetime64[ns]\n",
      " 11  volume              92306 non-null  float64       \n",
      "dtypes: datetime64[ns](2), float64(1), int64(3), object(6)\n",
      "memory usage: 8.5+ MB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Временной диапазон тренировочного множества:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Timestamp('2018-01-01 00:00:00'), Timestamp('2020-07-01 00:00:00'))"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.month.min(), data.month.max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Временные ряды"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_ts = data.groupby(AGG_COLS + [\"month\"])[\"volume\"].sum().unstack(fill_value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>2018-01-01</th>\n",
       "      <th>2018-02-01</th>\n",
       "      <th>2018-03-01</th>\n",
       "      <th>2018-04-01</th>\n",
       "      <th>2018-05-01</th>\n",
       "      <th>2018-06-01</th>\n",
       "      <th>2018-07-01</th>\n",
       "      <th>2018-08-01</th>\n",
       "      <th>2018-09-01</th>\n",
       "      <th>2018-10-01</th>\n",
       "      <th>2018-11-01</th>\n",
       "      <th>2018-12-01</th>\n",
       "      <th>2019-01-01</th>\n",
       "      <th>2019-02-01</th>\n",
       "      <th>2019-03-01</th>\n",
       "      <th>2019-04-01</th>\n",
       "      <th>2019-05-01</th>\n",
       "      <th>2019-06-01</th>\n",
       "      <th>2019-07-01</th>\n",
       "      <th>2019-08-01</th>\n",
       "      <th>2019-09-01</th>\n",
       "      <th>2019-10-01</th>\n",
       "      <th>2019-11-01</th>\n",
       "      <th>2019-12-01</th>\n",
       "      <th>2020-01-01</th>\n",
       "      <th>2020-02-01</th>\n",
       "      <th>2020-03-01</th>\n",
       "      <th>2020-04-01</th>\n",
       "      <th>2020-05-01</th>\n",
       "      <th>2020-06-01</th>\n",
       "      <th>2020-07-01</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>material_code</th>\n",
       "      <th>company_code</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>manager_code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <th>7278</th>\n",
       "      <th>Россия</th>\n",
       "      <th>Респ. Татарстан</th>\n",
       "      <th>17460</th>\n",
       "      <td>340.0</td>\n",
       "      <td>340.0</td>\n",
       "      <td>260.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>103.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">133</th>\n",
       "      <th rowspan=\"4\" valign=\"top\">0</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">Белоруссия</th>\n",
       "      <th>Минская обл.</th>\n",
       "      <th>10942</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>200.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>124.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>207.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>250.0</td>\n",
       "      <td>394.0</td>\n",
       "      <td>288.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>249.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Могилевская обл.</th>\n",
       "      <th>10942</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>103.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>166.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>г. Минск</th>\n",
       "      <th>10942</th>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Казахстан</th>\n",
       "      <th>г. Нур-Султан</th>\n",
       "      <th>13301</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>986</th>\n",
       "      <th>9943</th>\n",
       "      <th>Россия</th>\n",
       "      <th>Смоленская обл.</th>\n",
       "      <th>17460</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>125.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>83.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">998</th>\n",
       "      <th>0</th>\n",
       "      <th>Россия</th>\n",
       "      <th>Ленинградская обл.</th>\n",
       "      <th>18079</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3380</th>\n",
       "      <th>Россия</th>\n",
       "      <th>Ленинградская обл.</th>\n",
       "      <th>14956</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>121.0</td>\n",
       "      <td>121.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>117.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5410</th>\n",
       "      <th>Россия</th>\n",
       "      <th>г. Санкт-Петербург</th>\n",
       "      <th>14956</th>\n",
       "      <td>60.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>119.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6346</th>\n",
       "      <th>Россия</th>\n",
       "      <th>Респ. Башкортостан</th>\n",
       "      <th>10737</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>941 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "month                                                                  2018-01-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              340.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301                0.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                0.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956               60.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2018-02-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              340.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942               20.0   \n",
       "                           Казахстан  г. Нур-Султан      13301                0.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                0.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956               60.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2018-03-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              260.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               30.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                0.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              100.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2018-04-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              240.0   \n",
       "133           0            Белоруссия Минская обл.       10942              200.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               30.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                0.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956               60.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2018-05-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              220.0   \n",
       "133           0            Белоруссия Минская обл.       10942               60.0   \n",
       "                                      Могилевская обл.   10942              140.0   \n",
       "                                      г. Минск           10942               40.0   \n",
       "                           Казахстан  г. Нур-Султан      13301                0.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                0.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956               60.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2018-06-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              220.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942              160.0   \n",
       "                           Казахстан  г. Нур-Султан      13301                0.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                0.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956               80.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2018-07-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              220.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942              180.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               40.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                0.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956               80.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2018-08-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              220.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942               99.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               20.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                4.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              100.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2018-09-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              220.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942              100.0   \n",
       "                                      г. Минск           10942               60.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               40.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                5.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956               80.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2018-10-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              280.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942              220.0   \n",
       "                                      г. Минск           10942              400.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               30.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                5.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956               80.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2018-11-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              280.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942               20.0   \n",
       "                                      г. Минск           10942              120.0   \n",
       "                           Казахстан  г. Нур-Султан      13301                0.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                0.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956               80.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2018-12-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              280.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942               20.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               40.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                0.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              100.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2019-01-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              200.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942               40.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               40.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                5.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              120.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2019-02-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              200.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942               80.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               50.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                5.0   \n",
       "              3380         Россия     Ленинградская обл. 14956                0.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              119.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2019-03-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              200.0   \n",
       "133           0            Белоруссия Минская обл.       10942                0.0   \n",
       "                                      Могилевская обл.   10942              142.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301                0.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                0.0   \n",
       "              3380         Россия     Ленинградская обл. 14956               80.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              160.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737               20.0   \n",
       "\n",
       "month                                                                  2019-04-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              185.0   \n",
       "133           0            Белоруссия Минская обл.       10942               36.0   \n",
       "                                      Могилевская обл.   10942              103.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               40.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                8.0   \n",
       "              3380         Россия     Ленинградская обл. 14956               94.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              120.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2019-05-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460              103.0   \n",
       "133           0            Белоруссия Минская обл.       10942               98.0   \n",
       "                                      Могилевская обл.   10942              145.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301                0.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                5.0   \n",
       "              3380         Россия     Ленинградская обл. 14956              127.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              140.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737               20.0   \n",
       "\n",
       "month                                                                  2019-06-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460               62.0   \n",
       "133           0            Белоруссия Минская обл.       10942               82.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               40.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079               10.0   \n",
       "              3380         Россия     Ленинградская обл. 14956              121.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              100.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2019-07-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0   \n",
       "133           0            Белоруссия Минская обл.       10942               62.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               40.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                5.0   \n",
       "              3380         Россия     Ленинградская обл. 14956              121.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              120.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737               20.0   \n",
       "\n",
       "month                                                                  2019-08-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0   \n",
       "133           0            Белоруссия Минская обл.       10942              145.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942               41.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               40.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                5.0   \n",
       "              3380         Россия     Ленинградская обл. 14956              129.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              120.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2019-09-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0   \n",
       "133           0            Белоруссия Минская обл.       10942              124.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942               83.0   \n",
       "                           Казахстан  г. Нур-Султан      13301                0.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079               10.0   \n",
       "              3380         Россия     Ленинградская обл. 14956              117.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956               80.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737               20.0   \n",
       "\n",
       "month                                                                  2019-10-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0   \n",
       "133           0            Белоруссия Минская обл.       10942              181.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942               82.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               45.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079               10.0   \n",
       "              3380         Россия     Ленинградская обл. 14956              115.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              120.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737               40.0   \n",
       "\n",
       "month                                                                  2019-11-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0   \n",
       "133           0            Белоруссия Минская обл.       10942              208.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942               42.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               50.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                0.0   \n",
       "              3380         Россия     Ленинградская обл. 14956              102.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              140.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737               40.0   \n",
       "\n",
       "month                                                                  2019-12-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0   \n",
       "133           0            Белоруссия Минская обл.       10942              207.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               45.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                6.0   \n",
       "              3380         Россия     Ленинградская обл. 14956               29.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              100.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737               40.0   \n",
       "\n",
       "month                                                                  2020-01-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0   \n",
       "133           0            Белоруссия Минская обл.       10942               17.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301                0.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460                0.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                5.0   \n",
       "              3380         Россия     Ленинградская обл. 14956               73.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              100.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737               40.0   \n",
       "\n",
       "month                                                                  2020-02-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0   \n",
       "133           0            Белоруссия Минская обл.       10942               72.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               50.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460               21.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                5.0   \n",
       "              3380         Россия     Ленинградская обл. 14956               74.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              180.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737                0.0   \n",
       "\n",
       "month                                                                  2020-03-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0   \n",
       "133           0            Белоруссия Минская обл.       10942              250.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               40.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460               63.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                5.0   \n",
       "              3380         Россия     Ленинградская обл. 14956              122.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              180.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737               20.0   \n",
       "\n",
       "month                                                                  2020-04-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0   \n",
       "133           0            Белоруссия Минская обл.       10942              394.0   \n",
       "                                      Могилевская обл.   10942              166.0   \n",
       "                                      г. Минск           10942               21.0   \n",
       "                           Казахстан  г. Нур-Султан      13301                0.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460              125.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                0.0   \n",
       "              3380         Россия     Ленинградская обл. 14956              100.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              100.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737               40.0   \n",
       "\n",
       "month                                                                  2020-05-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0   \n",
       "133           0            Белоруссия Минская обл.       10942              288.0   \n",
       "                                      Могилевская обл.   10942               62.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301                0.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460               84.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                3.0   \n",
       "              3380         Россия     Ленинградская обл. 14956               15.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956              140.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737               20.0   \n",
       "\n",
       "month                                                                  2020-06-01  \\\n",
       "material_code company_code country    region             manager_code               \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0   \n",
       "133           0            Белоруссия Минская обл.       10942              210.0   \n",
       "                                      Могилевская обл.   10942                0.0   \n",
       "                                      г. Минск           10942                0.0   \n",
       "                           Казахстан  г. Нур-Султан      13301               50.0   \n",
       "...                                                                           ...   \n",
       "986           9943         Россия     Смоленская обл.    17460               84.0   \n",
       "998           0            Россия     Ленинградская обл. 18079                3.0   \n",
       "              3380         Россия     Ленинградская обл. 14956               30.0   \n",
       "              5410         Россия     г. Санкт-Петербург 14956               40.0   \n",
       "              6346         Россия     Респ. Башкортостан 10737               21.0   \n",
       "\n",
       "month                                                                  2020-07-01  \n",
       "material_code company_code country    region             manager_code              \n",
       "124           7278         Россия     Респ. Татарстан    17460                0.0  \n",
       "133           0            Белоруссия Минская обл.       10942              249.0  \n",
       "                                      Могилевская обл.   10942                0.0  \n",
       "                                      г. Минск           10942                6.0  \n",
       "                           Казахстан  г. Нур-Султан      13301                0.0  \n",
       "...                                                                           ...  \n",
       "986           9943         Россия     Смоленская обл.    17460               83.0  \n",
       "998           0            Россия     Ленинградская обл. 18079                9.0  \n",
       "              3380         Россия     Ленинградская обл. 14956               50.0  \n",
       "              5410         Россия     г. Санкт-Петербург 14956                0.0  \n",
       "              6346         Россия     Респ. Башкортостан 10737               21.0  \n",
       "\n",
       "[941 rows x 31 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group_ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CatBoostRegressor\n",
    "\n",
    "Признаки:\n",
    "\n",
    "- оригинальные категориальные признаки,\n",
    "- месяц, для которого предсказываем,\n",
    "- среднее, минимум и максимум за год,\n",
    "- последние 6 месяцев до месяца, для которого предсказываем.\n",
    "\n",
    "Для тренировки будем использовать период `2019-01-01` по `2019-06-01`, для валидации: с `2019-07-01` по `2019-12-01`, для тестирования: с `2020-01-01` по `2020-07-01`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_features(df: pd.DataFrame, month: pd.Timestamp, N=6,MNGR_GRP_MDAD=0,  MNGR_GRP_MDAD2=0) -> pd.DataFrame:  # ,  MNGR_GRP_MDAD=7)\n",
    "    \"\"\"Calculate features for `month`.\"\"\"\n",
    "\n",
    "    start_period = month - pd.offsets.MonthBegin(N)\n",
    "    end_period = month - pd.offsets.MonthBegin(1)\n",
    "\n",
    "    df = df.loc[:, :end_period]\n",
    "\n",
    "    features = pd.DataFrame([], index=df.index)\n",
    "    features[\"month\"] = month.month\n",
    "    # формируем лаги за N месяцев\n",
    "    features[[f\"vol_tm{i}\" for i in range(N, 0, -1)]] = df.loc[:, start_period:end_period].copy()\n",
    "\n",
    "    ### !!!!!!!!!!!!!!   #################################################################################\n",
    "    rolling = df.rolling(12, axis=1, min_periods=1)\n",
    "    features = features.join(rolling.mean().iloc[:, -1].rename(\"last_year_avg\"))\n",
    "    \n",
    "    # Добавление скользящих средних абсолютные отклонения(MDAD)\n",
    "    rolling = df.rolling(2, axis=1, min_periods=1)\n",
    "    features = features.join( \n",
    "                    rolling.apply(lambda x: np.nanmedian(np.abs(x - np.nanmedian(x))) , raw=True \n",
    "                    ).iloc[:, -1].rename(\"mdad2\") )\n",
    "   \n",
    "    # Добавление ГРУППОВЫХ скользящих средних абсолютные отклонения(MDAD)\n",
    "    if MNGR_GRP_MDAD != 0:\n",
    "        period = MNGR_GRP_MDAD\n",
    "        df2 = df.copy()\n",
    "        df2[df2.columns.to_list()] = \\\n",
    "                                df2.groupby(level='manager_code').transform(lambda x: x.mean())\n",
    "        grp_manager_roll_mean = df2.rolling(period, axis=1, min_periods=1)\n",
    "        features = \\\n",
    "        features.join(grp_manager_roll_mean.apply(lambda x: np.nanmedian(np.abs(x - np.nanmedian(x))) , raw=True \n",
    "                        ).iloc[:, -1].rename(\"MNGR_GRP_MDAD\"+str(period)))\n",
    "        \n",
    "    # Добавление ГРУППОВЫХ скользящих средних абсолютные отклонения(MDAD)\n",
    "    if MNGR_GRP_MDAD2 != 0:\n",
    "        period = MNGR_GRP_MDAD2\n",
    "        df2 = df.copy()\n",
    "        df2[df2.columns.to_list()] = \\\n",
    "                                df2.groupby(level='manager_code').transform(lambda x: x.mean())\n",
    "        grp_manager_roll_mean = df2.rolling(period, axis=1, min_periods=1)\n",
    "        features = \\\n",
    "        features.join(grp_manager_roll_mean.apply(lambda x: np.nanmedian(np.abs(x - np.nanmedian(x))) , raw=True \n",
    "                        ).iloc[:, -1].rename(\"MNGR_GRP_MDAD\"+str(period)))\n",
    "    ##################################################################################################\n",
    "    ##################################################################################################\n",
    "\n",
    "    # выделяем КВАРТАЛЫ\n",
    "    #features[\"quarter\"] = month.quarter\n",
    "    #features[\"month2\"] = month\n",
    "\n",
    "    return features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "tr_range = pd.date_range(\"2019-01-01\", \"2019-06-01\", freq=\"MS\")\n",
    "val_range = pd.date_range(\"2019-07-01\", \"2019-12-01\", freq=\"MS\")\n",
    "ts_range = pd.date_range(\"2020-01-01\", \"2020-07-01\", freq=\"MS\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_features = {}\n",
    "\n",
    "for dataset, dataset_range in zip([\"tr\", \"val\", \"ts\"], [tr_range, val_range, ts_range]):\n",
    "    dataset_features = []\n",
    "    for target_month in dataset_range:\n",
    "        features = get_features(group_ts, target_month)\n",
    "        features[\"target\"] = group_ts[target_month]\n",
    "        dataset_features.append(features.reset_index())\n",
    "    full_features[dataset] = pd.concat(dataset_features, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>material_code</th>\n",
       "      <th>company_code</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>manager_code</th>\n",
       "      <th>month</th>\n",
       "      <th>vol_tm6</th>\n",
       "      <th>vol_tm5</th>\n",
       "      <th>vol_tm4</th>\n",
       "      <th>vol_tm3</th>\n",
       "      <th>vol_tm2</th>\n",
       "      <th>vol_tm1</th>\n",
       "      <th>last_year_avg</th>\n",
       "      <th>mdad2</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>124</td>\n",
       "      <td>7278</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Респ. Татарстан</td>\n",
       "      <td>17460</td>\n",
       "      <td>1</td>\n",
       "      <td>220.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>260.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Белоруссия</td>\n",
       "      <td>Минская обл.</td>\n",
       "      <td>10942</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Белоруссия</td>\n",
       "      <td>Могилевская обл.</td>\n",
       "      <td>10942</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Белоруссия</td>\n",
       "      <td>г. Минск</td>\n",
       "      <td>10942</td>\n",
       "      <td>1</td>\n",
       "      <td>180.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>91.583333</td>\n",
       "      <td>50.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Казахстан</td>\n",
       "      <td>г. Нур-Султан</td>\n",
       "      <td>13301</td>\n",
       "      <td>1</td>\n",
       "      <td>40.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>19.166667</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   material_code  company_code     country            region  manager_code  \\\n",
       "0            124          7278      Россия   Респ. Татарстан         17460   \n",
       "1            133             0  Белоруссия      Минская обл.         10942   \n",
       "2            133             0  Белоруссия  Могилевская обл.         10942   \n",
       "3            133             0  Белоруссия          г. Минск         10942   \n",
       "4            133             0   Казахстан     г. Нур-Султан         13301   \n",
       "\n",
       "   month  vol_tm6  vol_tm5  vol_tm4  vol_tm3  vol_tm2  vol_tm1  last_year_avg  \\\n",
       "0      1    220.0    220.0    220.0    280.0    280.0    280.0     260.000000   \n",
       "1      1      0.0      0.0      0.0      0.0      0.0      0.0      21.666667   \n",
       "2      1      0.0      0.0    100.0    220.0     20.0      0.0      40.000000   \n",
       "3      1    180.0     99.0     60.0    400.0    120.0     20.0      91.583333   \n",
       "4      1     40.0     20.0     40.0     30.0      0.0     40.0      19.166667   \n",
       "\n",
       "   mdad2  target  \n",
       "0    0.0   200.0  \n",
       "1    0.0     0.0  \n",
       "2   10.0     0.0  \n",
       "3   50.0    40.0  \n",
       "4   20.0    40.0  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_features[\"tr\"].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "for dataset in [\"tr\", \"val\", \"ts\"]:\n",
    "    for c in full_features[dataset].columns:\n",
    "        col_type = full_features[dataset][c].dtype\n",
    "        if col_type == 'object': # or col_type == 'int64': # or col_type.name == 'category':\n",
    "            full_features[dataset][c] = full_features[dataset][c].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5646 entries, 0 to 5645\n",
      "Data columns (total 15 columns):\n",
      " #   Column         Non-Null Count  Dtype   \n",
      "---  ------         --------------  -----   \n",
      " 0   material_code  5646 non-null   int64   \n",
      " 1   company_code   5646 non-null   int64   \n",
      " 2   country        5646 non-null   category\n",
      " 3   region         5646 non-null   category\n",
      " 4   manager_code   5646 non-null   int64   \n",
      " 5   month          5646 non-null   int64   \n",
      " 6   vol_tm6        5646 non-null   float64 \n",
      " 7   vol_tm5        5646 non-null   float64 \n",
      " 8   vol_tm4        5646 non-null   float64 \n",
      " 9   vol_tm3        5646 non-null   float64 \n",
      " 10  vol_tm2        5646 non-null   float64 \n",
      " 11  vol_tm1        5646 non-null   float64 \n",
      " 12  last_year_avg  5646 non-null   float64 \n",
      " 13  mdad2          5646 non-null   float64 \n",
      " 14  target         5646 non-null   float64 \n",
      "dtypes: category(2), float64(9), int64(4)\n",
      "memory usage: 590.7 KB\n"
     ]
    }
   ],
   "source": [
    "full_features[\"tr\"].info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Тренировка"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "CAT_COLS = [\"material_code\", \"company_code\", \"country\", \"region\", \"manager_code\", \"month\"]\n",
    "#CAT_COLS3 = [\"material_code\", \"company_code\",  \"manager_code\", \"month\"]\n",
    "CAT_COLS2 = [\"country\", \"region\"]\n",
    "CAT_COLS_LGB = [\"name_material_code\", \"name_company_code\",  \"name_manager_code\", \"name_month\"]\n",
    "TARGET = \"target\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['material_code', 'company_code', 'country', 'region', 'manager_code', 'month', 'vol_tm6', 'vol_tm5', 'vol_tm4', 'vol_tm3', 'vol_tm2', 'vol_tm1', 'last_year_avg', 'mdad2']\n"
     ]
    }
   ],
   "source": [
    "# создаем список  переменных\n",
    "FTS_COLS = full_features[\"tr\"].columns.tolist()\n",
    "FTS_COLS.remove('target')\n",
    "print(FTS_COLS)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "encoder_1 = TargetEncoder()\n",
    "encoder_2 = TargetEncoder()\n",
    "\n",
    "full_features[\"tr\"][\"country\"] = encoder_1.fit_transform(full_features[\"tr\"][\"country\"], full_features[\"tr\"][TARGET])\n",
    "full_features[\"tr\"][\"region\"]  = encoder_2.fit_transform(full_features[\"tr\"][\"region\"], full_features[\"tr\"][TARGET])\n",
    "\n",
    "\n",
    "full_features[\"val\"][\"country\"] = encoder_1.transform(full_features[\"val\"][\"country\"])\n",
    "full_features[\"val\"][\"region\"]  = encoder_2.transform(full_features[\"val\"][\"region\"])\n",
    "\n",
    "full_features[\"ts\"][\"country\"] = encoder_1.transform(full_features[\"ts\"][\"country\"])\n",
    "full_features[\"ts\"][\"region\"]  = encoder_2.transform(full_features[\"ts\"][\"region\"])\n",
    "\n",
    "full_features[\"tr\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "FINAL_n_estimators = 30 #25 \n",
    "FINAL_subsample = 0.5\n",
    "FINAL_min_samples_leaf = 11 #25 \n",
    "FINAL_max_features = 1\n",
    "\n",
    "# [1.5403588928614802, 'ITER=>', 50, 'LEARNING_RATE->', 0.08, 'DEPTH=', 4]\n",
    "\n",
    "ITERATION_ctbst = 50              # 52  # 50 # T12(Cv12_i50i008d4.zip)\n",
    "LEARNING_RATE_ctbst = 0.08 #0.079  #     # 0.008 # T12(Cv12_i50i008d4.zip)\n",
    "DEPTH_ctbst =  4         # 3              # T12(Cv12_i50i008d4.zip)\n",
    "model = CatBoostRegressor(iterations=ITERATION_ctbst,  \n",
    "                          learning_rate=LEARNING_RATE_ctbst, \n",
    "                          depth=DEPTH_ctbst, \n",
    "                          cat_features=CAT_COLS,\n",
    "                          random_state=RS,\n",
    "                          verbose=0)\n",
    "\n",
    "\n",
    "# создаем экземпляр модели LGBMRegressor\n",
    "ITERATION_lgb = 34\n",
    "LEARNING_RATE_lgb = 0.0813 \n",
    "DEPTH_lgb = 3\n",
    "modelLGBM = lgb.LGBMRegressor(learning_rate=LEARNING_RATE_lgb,                          \n",
    "                               max_depth=DEPTH_lgb,\n",
    "                               n_estimators=ITERATION_lgb,\n",
    "                               subsample=0.8,\n",
    "                               colsample_bytree=1., \n",
    "                              #categorical_feature=CAT_COLS_LGB,\n",
    "                               random_state=RS)\n",
    "\n",
    "\n",
    "\n",
    "final_estimator = GradientBoostingRegressor(\n",
    "         n_estimators=FINAL_n_estimators, #25, \n",
    "        subsample=FINAL_subsample, # 0.5, \n",
    "        min_samples_leaf=FINAL_min_samples_leaf, # 25, \n",
    "        max_features=FINAL_max_features, # 1,\n",
    "             random_state=42)\n",
    "\n",
    "'''    \n",
    "final_estimator=RandomForestRegressor(n_estimators=FINAL_n_estimators,\n",
    "                      random_state=42)\n",
    "'''\n",
    "\n",
    "estimators =[('ctbst', model ),\n",
    "             ('lgb', modelLGBM ),\n",
    "             #('xgb', xgb_model)\n",
    "            ]\n",
    "\n",
    "reg = StackingRegressor(estimators=estimators,\n",
    "                        final_estimator=final_estimator,\n",
    "                        passthrough=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "StackingRegressor(estimators=[('ctbst',\n",
       "                               <catboost.core.CatBoostRegressor object at 0x000002360F3BE490>),\n",
       "                              ('lgb',\n",
       "                               LGBMRegressor(learning_rate=0.0813, max_depth=3,\n",
       "                                             n_estimators=34,\n",
       "                                             random_state=82736,\n",
       "                                             subsample=0.8))],\n",
       "                  final_estimator=GradientBoostingRegressor(max_features=1,\n",
       "                                                            min_samples_leaf=11,\n",
       "                                                            n_estimators=30,\n",
       "                                                            random_state=42,\n",
       "                                                            subsample=0.5))"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reg.fit(full_features[\"tr\"][FTS_COLS], \n",
    "          # обучаем модель, используя логарифмирование зависимой \n",
    "          np.log1p(full_features[\"tr\"][TARGET]), \n",
    "          #eval_set=(full_features[\"val\"][FTS_COLS], full_features[\"val\"][TARGET])\n",
    "         )"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "from scipy.special import expm1\n",
    "# получаем прогнозы, перед этим выполнив экспоненцирование - операцию,\n",
    "# обратную логарифмированию\n",
    "tr_preds = expm1(model.predict(full_features[\"tr\"][FTS_COLS]))\n",
    "val_preds = expm1(model.predict(full_features[\"val\"][FTS_COLS]))\n",
    "ts_preds = expm1(model.predict(full_features[\"ts\"][FTS_COLS]))\n",
    "tr_preds = pd.Series(tr_preds).clip(lower=0, upper=7000)\n",
    "val_preds = pd.Series(val_preds).clip(lower=0, upper=7000)\n",
    "ts_preds = pd.Series(ts_preds).clip(lower=0, upper=7000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# получаем прогнозы, перед этим выполнив экспоненцирование - операцию,\n",
    "# обратную логарифмированию\n",
    "tr_preds = np.expm1(reg.predict(full_features[\"tr\"][FTS_COLS]))\n",
    "val_preds = np.expm1(reg.predict(full_features[\"val\"][FTS_COLS]))\n",
    "ts_preds = np.expm1(reg.predict(full_features[\"ts\"][FTS_COLS]))\n",
    "tr_preds = pd.Series(tr_preds).clip(lower=0)\n",
    "val_preds = pd.Series(val_preds).clip(lower=0)\n",
    "ts_preds = pd.Series(ts_preds).clip(lower=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ошибка на тренировочном множестве: 1.4638\n",
      "Ошибка на валидационном множестве: 1.5456\n",
      "Ошибка на тестовом множестве: 1.7388\n"
     ]
    }
   ],
   "source": [
    "print(\"Ошибка на тренировочном множестве:\",\n",
    "      f'{np.sqrt(mean_squared_log_error(full_features[\"tr\"][TARGET], tr_preds)):.4f}')\n",
    "print(\"Ошибка на валидационном множестве:\",\n",
    "      f'{np.sqrt(mean_squared_log_error(full_features[\"val\"][TARGET], val_preds)):.4f}')\n",
    "print(\"Ошибка на тестовом множестве:\",\n",
    "      f'{np.sqrt(mean_squared_log_error(full_features[\"ts\"][TARGET], ts_preds)):.4f}')"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "ET12(Cv12_i50i008d4.zip)\n",
    "Ошибка на тренировочном множестве: 1.4775\n",
    "Ошибка на валидационном множестве: 1.5467\n",
    "Ошибка на тестовом множестве: 1.7407"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5646 entries, 0 to 5645\n",
      "Data columns (total 15 columns):\n",
      " #   Column         Non-Null Count  Dtype   \n",
      "---  ------         --------------  -----   \n",
      " 0   material_code  5646 non-null   int64   \n",
      " 1   company_code   5646 non-null   int64   \n",
      " 2   country        5646 non-null   category\n",
      " 3   region         5646 non-null   category\n",
      " 4   manager_code   5646 non-null   int64   \n",
      " 5   month          5646 non-null   int64   \n",
      " 6   vol_tm6        5646 non-null   float64 \n",
      " 7   vol_tm5        5646 non-null   float64 \n",
      " 8   vol_tm4        5646 non-null   float64 \n",
      " 9   vol_tm3        5646 non-null   float64 \n",
      " 10  vol_tm2        5646 non-null   float64 \n",
      " 11  vol_tm1        5646 non-null   float64 \n",
      " 12  last_year_avg  5646 non-null   float64 \n",
      " 13  mdad2          5646 non-null   float64 \n",
      " 14  target         5646 non-null   float64 \n",
      "dtypes: category(2), float64(9), int64(4)\n",
      "memory usage: 590.7 KB\n"
     ]
    }
   ],
   "source": [
    "full_features[\"tr\"].info()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "не с Эт\n",
    "Ошибка на тренировочном множестве: 1.4672\n",
    "Ошибка на валидационном множестве: 1.5485\n",
    "Ошибка на тестовом множестве: 1.7337"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Ошибка на тренировочном множестве: 1.4626\n",
    "Ошибка на валидационном множестве: 1.5480\n",
    "Ошибка на тестовом множестве: 1.7317"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Ошибка на тренировочном множестве: 1.4507\n",
    "Ошибка на валидационном множестве: 1.5547\n",
    "Ошибка на тестовом множестве: 1.7450"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Ошибка на тренировочном множестве: 1.4507\n",
    "Ошибка на валидационном множестве: 1.5547\n",
    "Ошибка на тестовом множестве: 1.7450"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ошибка на тренировочном множестве: 1.4884\n",
    "Ошибка на валидационном множестве: 1.5461\n",
    "Ошибка на тестовом множестве: 1.7368"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# найдено на работе - 4 TimeSeriasSplit\n",
    "# [1.6502302..., 'LEARNING_RATE->', 0.03, 'ITER=>', 199, 'DEPTH=', 4]\n",
    "ITER = 199 # 50 #250 #85\n",
    "LEARNING_RATE = 0.03 # 0.08 # 0.09 # 0.08\n",
    "DEPTH = 4 # 6 #4 \n",
    "Ошибка на тренировочном множестве: 1.4664\n",
    "Ошибка на валидационном множестве: 1.5436\n",
    "Ошибка на тестовом множестве: 1.7428"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# найдено на работе -5 KFold\n",
    "# [1.6026393..., 'LEARNING_RATE->', 0.025, 'ITER=>', 249, 'DEPTH=', 4]\n",
    "ITER = 249 # 50 #250 #85\n",
    "LEARNING_RATE = 0.025 # 0.08 # 0.09 # 0.08\n",
    "DEPTH = 4 # 6 #4 \n",
    "Ошибка на тренировочном множестве: 1.4388\n",
    "Ошибка на валидационном множестве: 1.5465\n",
    "Ошибка на тестовом множестве: 1.7457"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# Work  DEEP = 1\n",
    "Ошибка на тренировочном множестве: 1.5013\n",
    "Ошибка на валидационном множестве: 1.5450\n",
    "Ошибка на тестовом множестве: 1.7370"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# Et2\n",
    "Ошибка на тренировочном множестве: 1.4261\n",
    "Ошибка на валидационном множестве: 1.5500\n",
    "Ошибка на тестовом множестве: 1.7465"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "iterations=1000, learning_rate=0.09, \n",
    "# с ЛОГАРИФМИРОВАНИЕМ таргета!!!, БЕЗ last_year_min last_year_max, но с last_year_avg + mdad2 # Cv1\n",
    "Ошибка на тренировочном множестве: 1.3672\n",
    "Ошибка на валидационном множестве: 1.5497\n",
    "Ошибка на тестовом множестве: 1.7656"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# с ЛОГАРИФМИРОВАНИЕМ таргета!!!, БЕЗ last_year_min last_year_max, но с last_year_avg + mdad2 # Cv1\n",
    "Ошибка на тренировочном множестве: 1.2883\n",
    "Ошибка на валидационном множестве: 1.5700\n",
    "Ошибка на тестовом множестве: 1.7866"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "'MNGR_GRP_MDAD->', 16\n",
    "Ошибка на тренировочном множестве: 1.3362\n",
    "Ошибка на валидационном множестве: 1.5594\n",
    "Ошибка на тестовом множестве: 1.7691"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "'MNGR_GRP_MDAD->', 7\n",
    "Ошибка на тренировочном множестве: 1.3820\n",
    "Ошибка на валидационном множестве: 1.5459\n",
    "Ошибка на тестовом множестве: 1.7550"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# с ЛОГАРИФМИРОВАНИЕМ таргета!!!, БЕЗ last_year_min last_year_max, но с last_year_avg + mdad5 mdad4 \n",
    "Ошибка на тренировочном множестве: 1.3403\n",
    "Ошибка на валидационном множестве: 1.5639\n",
    "Ошибка на тестовом множестве: 1.7684"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# ORIG БЕЗ last_year_min last_year_max, но с last_year_avg + mdad5 mdad4 # Cv1\n",
    "Ошибка на тренировочном множестве: 1.8763\n",
    "Ошибка на валидационном множестве: 1.8526\n",
    "Ошибка на тестовом множестве: 2.0695"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# ORIG  \n",
    "Ошибка на тренировочном множестве: 2.0103\n",
    "Ошибка на валидационном множестве: 1.9403\n",
    "Ошибка на тестовом множестве: 2.1533"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ###########################################################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_features_Cv(df: pd.DataFrame, month: pd.Timestamp, N=6, MDAD_1=2,MNGR_GRP_MDAD=0,  MNGR_GRP_MDAD2=0) -> pd.DataFrame: # ,  MNGR_GRP_MDAD=7)\n",
    "    \"\"\"Calculate features for `month`.\"\"\"\n",
    "\n",
    "    start_period = month - pd.offsets.MonthBegin(N)\n",
    "    end_period = month - pd.offsets.MonthBegin(1)\n",
    "\n",
    "    df = df.loc[:, :end_period]\n",
    "\n",
    "    features = pd.DataFrame([], index=df.index)\n",
    "    features[\"month\"] = month.month\n",
    "    # формируем лаги за N месяцев\n",
    "    features[[f\"vol_tm{i}\" for i in range(N, 0, -1)]] = df.loc[:, start_period:end_period].copy()\n",
    "\n",
    "    ### !!!!!!!!!!!!!!   #################################################################################\n",
    "    rolling = df.rolling(12, axis=1, min_periods=1)\n",
    "    features = features.join(rolling.mean().iloc[:, -1].rename(\"last_year_avg\"))\n",
    "    \n",
    "    # Добавление скользящих средних абсолютные отклонения(MDAD)\n",
    "    period = MDAD_1\n",
    "    rolling = df.rolling(period, axis=1, min_periods=1)\n",
    "    features = features.join( \n",
    "                    rolling.apply(lambda x: np.nanmedian(np.abs(x - np.nanmedian(x))) , raw=True \n",
    "                    ).iloc[:, -1].rename(\"mdad\"+str(period)) )\n",
    "    \n",
    "    # Добавление ГРУППОВЫХ скользящих средних абсолютные отклонения(MDAD)\n",
    "    if MNGR_GRP_MDAD != 0:\n",
    "        period = MNGR_GRP_MDAD\n",
    "        df2 = df.copy()\n",
    "        df2[df2.columns.to_list()] = \\\n",
    "                                df2.groupby(level='manager_code').transform(lambda x: x.mean())\n",
    "        grp_manager_roll_mean = df2.rolling(period, axis=1, min_periods=1)\n",
    "        features = \\\n",
    "        features.join(grp_manager_roll_mean.apply(lambda x: np.nanmedian(np.abs(x - np.nanmedian(x))) , raw=True \n",
    "                        ).iloc[:, -1].rename(\"MNGR_GRP_MDAD\"+str(period)))\n",
    "        \n",
    "    # Добавление ГРУППОВЫХ скользящих средних абсолютные отклонения(MDAD)\n",
    "    if MNGR_GRP_MDAD2 != 0:\n",
    "        period = MNGR_GRP_MDAD2\n",
    "        df2 = df.copy()\n",
    "        df2[df2.columns.to_list()] = \\\n",
    "                                df2.groupby(level='manager_code').transform(lambda x: x.mean())\n",
    "        grp_manager_roll_mean = df2.rolling(period, axis=1, min_periods=1)\n",
    "        features = \\\n",
    "        features.join(grp_manager_roll_mean.apply(lambda x: np.nanmedian(np.abs(x - np.nanmedian(x))) , raw=True \n",
    "                        ).iloc[:, -1].rename(\"MNGR_GRP_MDAD\"+str(period)))\n",
    "    ##################################################################################################\n",
    "    ##################################################################################################\n",
    "\n",
    "    # выделяем КВАРТАЛЫ\n",
    "    #features[\"quarter\"] = month.quarter\n",
    "\n",
    "    return features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['material_code', 'company_code', 'country', 'region', 'manager_code', 'month', 'vol_tm6', 'vol_tm5', 'vol_tm4', 'vol_tm3', 'vol_tm2', 'vol_tm1', 'last_year_avg', 'mdad2']\n"
     ]
    }
   ],
   "source": [
    "full_features2 = {}\n",
    "dataset_range = pd.date_range( \"2019-01-01\", group_ts.columns[-1], freq=\"MS\")\n",
    "dataset_features2 = []\n",
    "for target_month in dataset_range:\n",
    "    #print(target_month)\n",
    "    features2 = get_features_Cv(group_ts, target_month, MDAD_1=2,  MNGR_GRP_MDAD=0,  MNGR_GRP_MDAD2=0) # ,  MNGR_GRP_MDAD=7)\n",
    "    features2[\"target\"] = group_ts[target_month]\n",
    "    dataset_features2.append(features2.reset_index())\n",
    "full_features2 = pd.concat(dataset_features2, ignore_index=True)\n",
    "print(FTS_COLS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>material_code</th>\n",
       "      <th>company_code</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>manager_code</th>\n",
       "      <th>month</th>\n",
       "      <th>vol_tm6</th>\n",
       "      <th>vol_tm5</th>\n",
       "      <th>vol_tm4</th>\n",
       "      <th>vol_tm3</th>\n",
       "      <th>vol_tm2</th>\n",
       "      <th>vol_tm1</th>\n",
       "      <th>last_year_avg</th>\n",
       "      <th>mdad2</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>124</td>\n",
       "      <td>7278</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Респ. Татарстан</td>\n",
       "      <td>17460</td>\n",
       "      <td>1</td>\n",
       "      <td>220.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>260.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Белоруссия</td>\n",
       "      <td>Минская обл.</td>\n",
       "      <td>10942</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.666667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Белоруссия</td>\n",
       "      <td>Могилевская обл.</td>\n",
       "      <td>10942</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Белоруссия</td>\n",
       "      <td>г. Минск</td>\n",
       "      <td>10942</td>\n",
       "      <td>1</td>\n",
       "      <td>180.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>91.583333</td>\n",
       "      <td>50.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Казахстан</td>\n",
       "      <td>г. Нур-Султан</td>\n",
       "      <td>13301</td>\n",
       "      <td>1</td>\n",
       "      <td>40.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>19.166667</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17874</th>\n",
       "      <td>986</td>\n",
       "      <td>9943</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Смоленская обл.</td>\n",
       "      <td>17460</td>\n",
       "      <td>7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>125.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>31.416667</td>\n",
       "      <td>0.0</td>\n",
       "      <td>83.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17875</th>\n",
       "      <td>998</td>\n",
       "      <td>0</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Ленинградская обл.</td>\n",
       "      <td>18079</td>\n",
       "      <td>7</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.750000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17876</th>\n",
       "      <td>998</td>\n",
       "      <td>3380</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Ленинградская обл.</td>\n",
       "      <td>14956</td>\n",
       "      <td>7</td>\n",
       "      <td>73.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>85.583333</td>\n",
       "      <td>7.5</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17877</th>\n",
       "      <td>998</td>\n",
       "      <td>5410</td>\n",
       "      <td>Россия</td>\n",
       "      <td>г. Санкт-Петербург</td>\n",
       "      <td>14956</td>\n",
       "      <td>7</td>\n",
       "      <td>100.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>118.333333</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17878</th>\n",
       "      <td>998</td>\n",
       "      <td>6346</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Респ. Башкортостан</td>\n",
       "      <td>10737</td>\n",
       "      <td>7</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>25.083333</td>\n",
       "      <td>0.5</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17879 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       material_code  company_code     country              region  \\\n",
       "0                124          7278      Россия     Респ. Татарстан   \n",
       "1                133             0  Белоруссия        Минская обл.   \n",
       "2                133             0  Белоруссия    Могилевская обл.   \n",
       "3                133             0  Белоруссия            г. Минск   \n",
       "4                133             0   Казахстан       г. Нур-Султан   \n",
       "...              ...           ...         ...                 ...   \n",
       "17874            986          9943      Россия     Смоленская обл.   \n",
       "17875            998             0      Россия  Ленинградская обл.   \n",
       "17876            998          3380      Россия  Ленинградская обл.   \n",
       "17877            998          5410      Россия  г. Санкт-Петербург   \n",
       "17878            998          6346      Россия  Респ. Башкортостан   \n",
       "\n",
       "       manager_code  month  vol_tm6  vol_tm5  vol_tm4  vol_tm3  vol_tm2  \\\n",
       "0             17460      1    220.0    220.0    220.0    280.0    280.0   \n",
       "1             10942      1      0.0      0.0      0.0      0.0      0.0   \n",
       "2             10942      1      0.0      0.0    100.0    220.0     20.0   \n",
       "3             10942      1    180.0     99.0     60.0    400.0    120.0   \n",
       "4             13301      1     40.0     20.0     40.0     30.0      0.0   \n",
       "...             ...    ...      ...      ...      ...      ...      ...   \n",
       "17874         17460      7      0.0     21.0     63.0    125.0     84.0   \n",
       "17875         18079      7      5.0      5.0      5.0      0.0      3.0   \n",
       "17876         14956      7     73.0     74.0    122.0    100.0     15.0   \n",
       "17877         14956      7    100.0    180.0    180.0    100.0    140.0   \n",
       "17878         10737      7     40.0      0.0     20.0     40.0     20.0   \n",
       "\n",
       "       vol_tm1  last_year_avg  mdad2  target  \n",
       "0        280.0     260.000000    0.0   200.0  \n",
       "1          0.0      21.666667    0.0     0.0  \n",
       "2          0.0      40.000000   10.0     0.0  \n",
       "3         20.0      91.583333   50.0    40.0  \n",
       "4         40.0      19.166667   20.0    40.0  \n",
       "...        ...            ...    ...     ...  \n",
       "17874     84.0      31.416667    0.0    83.0  \n",
       "17875      3.0       4.750000    0.0     9.0  \n",
       "17876     30.0      85.583333    7.5    50.0  \n",
       "17877     40.0     118.333333   50.0     0.0  \n",
       "17878     21.0      25.083333    0.5    21.0  \n",
       "\n",
       "[17879 rows x 15 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_features2"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "encoder_1 = TargetEncoder()\n",
    "encoder_2 = TargetEncoder()\n",
    "\n",
    "full_features2[\"country\"] = encoder_1.fit_transform(full_features2[\"country\"], full_features2[TARGET])\n",
    "full_features2[\"region\"]  = encoder_2.fit_transform(full_features2[\"region\"], full_features2[TARGET])\n",
    "\n",
    "full_features2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 17879 entries, 0 to 17878\n",
      "Data columns (total 15 columns):\n",
      " #   Column         Non-Null Count  Dtype   \n",
      "---  ------         --------------  -----   \n",
      " 0   material_code  17879 non-null  int64   \n",
      " 1   company_code   17879 non-null  int64   \n",
      " 2   country        17879 non-null  category\n",
      " 3   region         17879 non-null  category\n",
      " 4   manager_code   17879 non-null  int64   \n",
      " 5   month          17879 non-null  int64   \n",
      " 6   vol_tm6        17879 non-null  float64 \n",
      " 7   vol_tm5        17879 non-null  float64 \n",
      " 8   vol_tm4        17879 non-null  float64 \n",
      " 9   vol_tm3        17879 non-null  float64 \n",
      " 10  vol_tm2        17879 non-null  float64 \n",
      " 11  vol_tm1        17879 non-null  float64 \n",
      " 12  last_year_avg  17879 non-null  float64 \n",
      " 13  mdad2          17879 non-null  float64 \n",
      " 14  target         17879 non-null  float64 \n",
      "dtypes: category(2), float64(9), int64(4)\n",
      "memory usage: 1.8 MB\n"
     ]
    }
   ],
   "source": [
    "for c in full_features2.columns:\n",
    "    col_type = full_features2[c].dtype\n",
    "    if col_type == 'object': # or col_type == 'int64': # or col_type.name == 'category':\n",
    "        full_features2[c] = full_features2[c].astype('category')\n",
    "full_features2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "FINAL_n_estimators = 30 #25 \n",
    "FINAL_subsample = 0.5\n",
    "FINAL_min_samples_leaf = 11 #25 \n",
    "FINAL_max_features = 1\n",
    "\n",
    "# [1.5403588928614802, 'ITER=>', 50, 'LEARNING_RATE->', 0.08, 'DEPTH=', 4]\n",
    "\n",
    "ITERATION_ctbst = 50 # 52  #50              #  50 # T12(Cv12_i50i008d4.zip)\n",
    "LEARNING_RATE_ctbst = 0.08 # 0.079  #0.008 #     # 0.008 # T12(Cv12_i50i008d4.zip)\n",
    "DEPTH_ctbst = 4         # 3              #4         #  T12(Cv12_i50i008d4.zip)\n",
    "model = CatBoostRegressor(iterations=ITERATION_ctbst,  \n",
    "                          learning_rate=LEARNING_RATE_ctbst, \n",
    "                          depth=DEPTH_ctbst, \n",
    "                          cat_features=CAT_COLS,\n",
    "                          random_state=RS,\n",
    "                          verbose=0)\n",
    "\n",
    "\n",
    "# создаем экземпляр модели LGBMRegressor\n",
    "ITERATION_lgb = 34\n",
    "LEARNING_RATE_lgb = 0.0813 \n",
    "DEPTH_lgb = 3\n",
    "modelLGBM = lgb.LGBMRegressor(learning_rate=LEARNING_RATE_lgb,                          \n",
    "                               max_depth=DEPTH_lgb,\n",
    "                               n_estimators=ITERATION_lgb,\n",
    "                               subsample=0.8,\n",
    "                               colsample_bytree=1.,\n",
    "                               random_state=RS)\n",
    "\n",
    "\n",
    "\n",
    "final_estimator = GradientBoostingRegressor(\n",
    "         n_estimators=FINAL_n_estimators, #25, \n",
    "        subsample=FINAL_subsample, # 0.5, \n",
    "        min_samples_leaf=FINAL_min_samples_leaf, # 25, \n",
    "        max_features=FINAL_max_features, # 1,\n",
    "             random_state=42)\n",
    "\n",
    "'''    \n",
    "final_estimator=RandomForestRegressor(n_estimators=FINAL_n_estimators,\n",
    "                      random_state=42)\n",
    "'''\n",
    "\n",
    "estimators =[('ctbst', model ),\n",
    "             ('lgb', modelLGBM ),\n",
    "             #('xgb', xgb_model)\n",
    "            ]\n",
    "\n",
    "reg = StackingRegressor(estimators=estimators,\n",
    "                        final_estimator=final_estimator,\n",
    "                        passthrough=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "StackingRegressor(estimators=[('ctbst',\n",
       "                               <catboost.core.CatBoostRegressor object at 0x000002360F4490A0>),\n",
       "                              ('lgb',\n",
       "                               LGBMRegressor(learning_rate=0.0813, max_depth=3,\n",
       "                                             n_estimators=34,\n",
       "                                             random_state=82736,\n",
       "                                             subsample=0.8))],\n",
       "                  final_estimator=GradientBoostingRegressor(max_features=1,\n",
       "                                                            min_samples_leaf=11,\n",
       "                                                            n_estimators=30,\n",
       "                                                            random_state=42,\n",
       "                                                            subsample=0.5))"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reg.fit(full_features2[FTS_COLS],\n",
    "              # обучаем модель, используя логарифмирование зависимой \n",
    "              np.log1p(full_features2[TARGET]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "import dill\n",
    "\n",
    "#dill.dump(encoder_1, file = open(\"ET_encoder_1.pkl\", \"wb\"))\n",
    "#dill.dump(encoder_2, file = open(\"ET_encoder_2.pkl\", \"wb\"))\n",
    "\n",
    "dill.dump(reg, file = open(\"STACK_1.cbm\", \"wb\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting predict.py\n"
     ]
    }
   ],
   "source": [
    "%%writefile predict.py\n",
    "\n",
    "import pathlib\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from catboost import CatBoostRegressor\n",
    "import lightgbm as lgb\n",
    "\n",
    "import dill\n",
    "#from category_encoders import TargetEncoder #OrdinalEncoder\n",
    "#from sklearn.preprocessing import LabelEncoder\n",
    "\n",
    "MODEL_FILE = pathlib.Path(__file__).parent.joinpath(\"STACK_1.cbm\")\n",
    "#ENCODER_1_FILE = pathlib.Path(__file__).parent.joinpath(\"ET_encoder_1.pkl\")\n",
    "#ENCODER_2_FILE = pathlib.Path(__file__).parent.joinpath(\"ET_encoder_2.pkl\")\n",
    "\n",
    "AGG_COLS = [\"material_code\", \"company_code\", \"country\", \"region\", \"manager_code\"]\n",
    "CAT_COLS = [\"material_code\", \"company_code\", \"country\", \"region\", \"manager_code\", \"month\"]\n",
    "FTS_COLS = ['material_code', 'company_code', 'country', 'region', 'manager_code', 'month', \n",
    "            'vol_tm6', 'vol_tm5', 'vol_tm4', 'vol_tm3', 'vol_tm2', 'vol_tm1', 'last_year_avg', 'mdad2']\n",
    "\n",
    "TARGET = \"target\"\n",
    "\n",
    "def get_features(df: pd.DataFrame, month: pd.Timestamp) -> pd.DataFrame:\n",
    "    \"\"\"Вычисление признаков для `month`.\"\"\"\n",
    "\n",
    "    start_period = month - pd.offsets.MonthBegin(6)\n",
    "    end_period = month - pd.offsets.MonthBegin(1)\n",
    "\n",
    "    df = df.loc[:, :end_period]\n",
    "\n",
    "    features = pd.DataFrame([], index=df.index)\n",
    "    features[\"month\"] = month.month\n",
    "    features[[f\"vol_tm{i}\" for i in range(6, 0, -1)]] = df.loc[:, start_period:end_period].copy()\n",
    "\n",
    "   ### !!!!!!!!!!!!!!   #################################################################################\n",
    "    rolling = df.rolling(12, axis=1, min_periods=1)\n",
    "    features = features.join(rolling.mean().iloc[:, -1].rename(\"last_year_avg\"))\n",
    "    \n",
    "    # Добавление скользящих средних абсолютные отклонения(MDAD)\n",
    "    rolling = df.rolling(2, axis=1, min_periods=1)\n",
    "    features = features.join( \n",
    "                    rolling.apply(lambda x: np.nanmedian(np.abs(x - np.nanmedian(x))) , raw=True \n",
    "                    ).iloc[:, -1].rename(\"mdad2\") )\n",
    "    \n",
    "    return features.reset_index()\n",
    "\n",
    "\n",
    "def predict(df: pd.DataFrame, month: pd.Timestamp) -> pd.DataFrame:\n",
    "\n",
    "    model = dill.load(open(MODEL_FILE, \"rb\"))\n",
    "    #encoder_1 =  dill.load(open(ENCODER_1_FILE, \"rb\"))\n",
    "    #encoder_2 =  dill.load(open(ENCODER_2_FILE, \"rb\"))\n",
    "    \n",
    "    \n",
    "    group_ts = df.groupby(AGG_COLS + [\"month\"])[\"volume\"].sum().unstack(fill_value=0)\n",
    "    features = get_features(group_ts, month)\n",
    "    for c in features.columns:\n",
    "        col_type = features[c].dtype\n",
    "        if col_type == 'object': \n",
    "            features[c] = features[c].astype('category')    \n",
    "    \n",
    "    #features[CAT_COLS] = encoder.transform(features[CAT_COLS])\n",
    "    #features[\"country\"] = encoder_1.transform(features[\"country\"])\n",
    "    #features[\"region\"]  = encoder_2.transform(features[\"region\"])\n",
    "    \n",
    "    predictions = np.expm1(model.predict(features[FTS_COLS]))\n",
    "\n",
    "    preds_df = features[AGG_COLS].copy()\n",
    "    preds_df[\"prediction\"] = predictions\n",
    "    return preds_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<module 'predict' from 'C:\\\\Users\\\\dimacv\\\\PROJECTS\\\\Соревнования\\\\Sibur2021\\\\predict.py'>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import predict\n",
    "import importlib\n",
    "importlib.reload(predict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>material_code</th>\n",
       "      <th>company_code</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>manager_code</th>\n",
       "      <th>month</th>\n",
       "      <th>vol_tm6</th>\n",
       "      <th>vol_tm5</th>\n",
       "      <th>vol_tm4</th>\n",
       "      <th>vol_tm3</th>\n",
       "      <th>vol_tm2</th>\n",
       "      <th>vol_tm1</th>\n",
       "      <th>last_year_avg</th>\n",
       "      <th>mdad2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>124</td>\n",
       "      <td>7278</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Респ. Татарстан</td>\n",
       "      <td>17460</td>\n",
       "      <td>7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Белоруссия</td>\n",
       "      <td>Минская обл.</td>\n",
       "      <td>10942</td>\n",
       "      <td>7</td>\n",
       "      <td>17.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>250.0</td>\n",
       "      <td>394.0</td>\n",
       "      <td>288.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>179.833333</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Белоруссия</td>\n",
       "      <td>Могилевская обл.</td>\n",
       "      <td>10942</td>\n",
       "      <td>7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>166.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.000000</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Белоруссия</td>\n",
       "      <td>г. Минск</td>\n",
       "      <td>10942</td>\n",
       "      <td>7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22.416667</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Казахстан</td>\n",
       "      <td>г. Нур-Султан</td>\n",
       "      <td>13301</td>\n",
       "      <td>7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>936</th>\n",
       "      <td>986</td>\n",
       "      <td>9943</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Смоленская обл.</td>\n",
       "      <td>17460</td>\n",
       "      <td>7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>125.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>31.416667</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>937</th>\n",
       "      <td>998</td>\n",
       "      <td>0</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Ленинградская обл.</td>\n",
       "      <td>18079</td>\n",
       "      <td>7</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.750000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>938</th>\n",
       "      <td>998</td>\n",
       "      <td>3380</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Ленинградская обл.</td>\n",
       "      <td>14956</td>\n",
       "      <td>7</td>\n",
       "      <td>73.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>85.583333</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>939</th>\n",
       "      <td>998</td>\n",
       "      <td>5410</td>\n",
       "      <td>Россия</td>\n",
       "      <td>г. Санкт-Петербург</td>\n",
       "      <td>14956</td>\n",
       "      <td>7</td>\n",
       "      <td>100.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>118.333333</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>940</th>\n",
       "      <td>998</td>\n",
       "      <td>6346</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Респ. Башкортостан</td>\n",
       "      <td>10737</td>\n",
       "      <td>7</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>25.083333</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>941 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     material_code  company_code     country              region  \\\n",
       "0              124          7278      Россия     Респ. Татарстан   \n",
       "1              133             0  Белоруссия        Минская обл.   \n",
       "2              133             0  Белоруссия    Могилевская обл.   \n",
       "3              133             0  Белоруссия            г. Минск   \n",
       "4              133             0   Казахстан       г. Нур-Султан   \n",
       "..             ...           ...         ...                 ...   \n",
       "936            986          9943      Россия     Смоленская обл.   \n",
       "937            998             0      Россия  Ленинградская обл.   \n",
       "938            998          3380      Россия  Ленинградская обл.   \n",
       "939            998          5410      Россия  г. Санкт-Петербург   \n",
       "940            998          6346      Россия  Респ. Башкортостан   \n",
       "\n",
       "     manager_code  month  vol_tm6  vol_tm5  vol_tm4  vol_tm3  vol_tm2  \\\n",
       "0           17460      7      0.0      0.0      0.0      0.0      0.0   \n",
       "1           10942      7     17.0     72.0    250.0    394.0    288.0   \n",
       "2           10942      7      0.0      0.0      0.0    166.0     62.0   \n",
       "3           10942      7      0.0      0.0      0.0     21.0      0.0   \n",
       "4           13301      7      0.0     50.0     40.0      0.0      0.0   \n",
       "..            ...    ...      ...      ...      ...      ...      ...   \n",
       "936         17460      7      0.0     21.0     63.0    125.0     84.0   \n",
       "937         18079      7      5.0      5.0      5.0      0.0      3.0   \n",
       "938         14956      7     73.0     74.0    122.0    100.0     15.0   \n",
       "939         14956      7    100.0    180.0    180.0    100.0    140.0   \n",
       "940         10737      7     40.0      0.0     20.0     40.0     20.0   \n",
       "\n",
       "     vol_tm1  last_year_avg  mdad2  \n",
       "0        0.0       0.000000    0.0  \n",
       "1      210.0     179.833333   39.0  \n",
       "2        0.0      19.000000   31.0  \n",
       "3        0.0      22.416667    0.0  \n",
       "4       50.0      30.000000   25.0  \n",
       "..       ...            ...    ...  \n",
       "936     84.0      31.416667    0.0  \n",
       "937      3.0       4.750000    0.0  \n",
       "938     30.0      85.583333    7.5  \n",
       "939     40.0     118.333333   50.0  \n",
       "940     21.0      25.083333    0.5  \n",
       "\n",
       "[941 rows x 14 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "predict.get_features(group_ts.iloc[:, :-1], pd.Timestamp(\"2020-07-01\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>material_code</th>\n",
       "      <th>company_code</th>\n",
       "      <th>country</th>\n",
       "      <th>region</th>\n",
       "      <th>manager_code</th>\n",
       "      <th>prediction</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>124</td>\n",
       "      <td>7278</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Респ. Татарстан</td>\n",
       "      <td>17460</td>\n",
       "      <td>1.119568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Белоруссия</td>\n",
       "      <td>Минская обл.</td>\n",
       "      <td>10942</td>\n",
       "      <td>133.063111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Белоруссия</td>\n",
       "      <td>Могилевская обл.</td>\n",
       "      <td>10942</td>\n",
       "      <td>6.955001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Белоруссия</td>\n",
       "      <td>г. Минск</td>\n",
       "      <td>10942</td>\n",
       "      <td>1.815469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>Казахстан</td>\n",
       "      <td>г. Нур-Султан</td>\n",
       "      <td>13301</td>\n",
       "      <td>15.046590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>936</th>\n",
       "      <td>986</td>\n",
       "      <td>9943</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Смоленская обл.</td>\n",
       "      <td>17460</td>\n",
       "      <td>40.790878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>937</th>\n",
       "      <td>998</td>\n",
       "      <td>0</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Ленинградская обл.</td>\n",
       "      <td>18079</td>\n",
       "      <td>2.157092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>938</th>\n",
       "      <td>998</td>\n",
       "      <td>3380</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Ленинградская обл.</td>\n",
       "      <td>14956</td>\n",
       "      <td>23.421482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>939</th>\n",
       "      <td>998</td>\n",
       "      <td>5410</td>\n",
       "      <td>Россия</td>\n",
       "      <td>г. Санкт-Петербург</td>\n",
       "      <td>14956</td>\n",
       "      <td>60.735707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>940</th>\n",
       "      <td>998</td>\n",
       "      <td>6346</td>\n",
       "      <td>Россия</td>\n",
       "      <td>Респ. Башкортостан</td>\n",
       "      <td>10737</td>\n",
       "      <td>13.010769</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>941 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     material_code  company_code     country              region  \\\n",
       "0              124          7278      Россия     Респ. Татарстан   \n",
       "1              133             0  Белоруссия        Минская обл.   \n",
       "2              133             0  Белоруссия    Могилевская обл.   \n",
       "3              133             0  Белоруссия            г. Минск   \n",
       "4              133             0   Казахстан       г. Нур-Султан   \n",
       "..             ...           ...         ...                 ...   \n",
       "936            986          9943      Россия     Смоленская обл.   \n",
       "937            998             0      Россия  Ленинградская обл.   \n",
       "938            998          3380      Россия  Ленинградская обл.   \n",
       "939            998          5410      Россия  г. Санкт-Петербург   \n",
       "940            998          6346      Россия  Респ. Башкортостан   \n",
       "\n",
       "     manager_code  prediction  \n",
       "0           17460    1.119568  \n",
       "1           10942  133.063111  \n",
       "2           10942    6.955001  \n",
       "3           10942    1.815469  \n",
       "4           13301   15.046590  \n",
       "..            ...         ...  \n",
       "936         17460   40.790878  \n",
       "937         18079    2.157092  \n",
       "938         14956   23.421482  \n",
       "939         14956   60.735707  \n",
       "940         10737   13.010769  \n",
       "\n",
       "[941 rows x 6 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts_preds_tst = predict.predict(data[data.month<\"2020-07-01\"], pd.Timestamp(\"2020-07-01\"))\n",
    "ts_preds_tst "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ошибка на тестовом множестве: 1.5530\n"
     ]
    }
   ],
   "source": [
    "print(\"Ошибка на тестовом множестве:\",\n",
    "      f'{np.sqrt(mean_squared_log_error(group_ts.reset_index().iloc[:,-1], ts_preds_tst[\"prediction\"])):.4f}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Упаковка в zip\n",
    "!tar.exe -a -c -f Stack_31.zip STACK_1.cbm requirements.txt predict.py"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
